
SELECT 
	MES_CREDITO
	, SUM(BXA_VALOR_UNICA) UNICA
	, SUM(BXA_VALOR_DEMAIS) DEMAIS
	FROM 
		(
		SELECT
			  IMV.IMV_COD
			, IMV.LGD_COD
			, LTM_COD
			, MONTH(BXA_DATA_CREDITO) AS MES_CREDITO
			, BXA_VALOR	
			, ( CASE WHEN LAN_PARCELA = 0 THEN BXA_VALOR ELSE 0 END ) AS BXA_VALOR_UNICA
			, ( CASE WHEN LAN_PARCELA > 0 THEN BXA_VALOR ELSE 0 END ) AS BXA_VALOR_DEMAIS
			FROM TRB_BAIXA		BX
			JOIN TRB_LANCAMENTO LAN ON LAN.LAN_COD = BX.LAN_COD
			JOIN TRB_IPTU		IPT	ON IPT.TLA_COD = LAN.TLA_COD
			JOIN TRB_IMOVEL 	IMV ON IMV.IMV_COD = IPT.IMV_COD
		) A
	GROUP BY
	MES_CREDITO
	ORDER BY 1


SELECT 
	TAXA.TLA_COD
	, TAXA.PLA_COD 
	, PLA_SIGLA
	, IIR_PARCELA
	, (CASE WHEN IIR_PARCELA = 0 THEN IIR_VALOR ELSE 0 END) AS IIR_VALOR_UNICA
	, (CASE WHEN IIR_PARCELA > 0 THEN IIR_VALOR ELSE 0 END) AS IIR_VALOR_DEMAIS
	, (CASE WHEN BXA_DATA_ARRECADACAO IS NOT NULL AND IIR_PARCELA = 0 THEN IIR_VALOR ELSE 0 END) AS IIR_VALOR_UNICA_PAGA
	, (CASE WHEN BXA_DATA_ARRECADACAO IS NOT NULL AND IIR_PARCELA > 0 THEN IIR_VALOR ELSE 0 END) AS IIR_VALOR_DEMAIS_PAGA
	FROM TRB_IPTU_TAXA 					TAXA 
	INNER JOIN GER_PLANO_DE_CONTAS_VI 	GPC1 ON GPC1.PLA_COD = TAXA.PLA_COD 
	INNER JOIN TRB_LANCAMENTO 			LANC ON LANC.TLA_COD = TAXA.TLA_COD AND LANC.LAN_PARCELA = IIR_PARCELA
	LEFT  JOIN TRB_BAIXA 				BXA  ON BXA.LAN_COD  = LANC.LAN_COD

	ORDER BY taxa.TLA_COD, TAXA.PLA_COD, IIR_PARCELA

SELECT * FROM GER_PLANO_DE_CONTAS 
SELECT * FROM TRB_TRIBUTO_TAXA TAXA 
SELECT * FROM TRB_TIPO_TRIBUTO

SELECT * FROM TRB_IPTU_TAXA

SELECT *
	FROM TRB_BAIXA		BX
	JOIN TRB_LANCAMENTO LAN ON LAN.LAN_COD = BX.LAN_COD
	JOIN TRB_IPTU		IPT	ON IPT.TLA_COD = LAN.TLA_COD
	JOIN TRB_IMOVEL 	IMV ON IMV.IMV_COD = IPT.IMV_COD
	WHERE BXA_VALOR = 197.34



SELECT 
       MONTH( BXA_DATA_CREDITO ) MONTH_BXA_DATA_CREDITO, 
       SUM( CASE WHEN LAN_PARCELA = 0 THEN BXA_VALOR ELSE 0 END ) SUM_CASE_WHEN_LAN_PARCELA, 
       SUM( CASE WHEN LAN_PARCELA > 0 THEN BXA_VALOR ELSE 0 END ) SUM_CASE_WHEN_LAN_PARCE_2
FROM TRB_BAIXA TRB_BAIXA
      INNER JOIN TRB_LANCAMENTO TRB_LANCAMENTO ON 
     (TRB_LANCAMENTO.LAN_COD = TRB_BAIXA.LAN_COD)
      INNER JOIN TRB_IPTU TRB_IPTU ON 
     (TRB_IPTU.TLA_COD = TRB_LANCAMENTO.TLA_COD)
      INNER JOIN TRB_IMOVEL TRB_IMOVEL ON 
     (TRB_IMOVEL.IMV_COD = TRB_IPTU.IMV_COD)
GROUP BY MONTH( BXA_DATA_CREDITO ) -- TRB_BAIXA.BXA_DATA_CREDITO
order by 1


-- View 

CREATE VIEW DBO.TRB_IPTU_ARRECADACAO_VI AS
SELECT
	  IMV.IMV_COD
	, IMV.LGD_COD
	, LTM_COD
	, MONTH(BXA_DATA_CREDITO) AS MES_CREDITO
	, BXA_VALOR	
	, ( CASE WHEN LAN_PARCELA = 0 THEN BXA_VALOR ELSE 0 END ) AS BXA_VALOR_UNICA
	, ( CASE WHEN LAN_PARCELA > 0 THEN BXA_VALOR ELSE 0 END ) AS BXA_VALOR_DEMAIS
	FROM TRB_BAIXA		BX
	JOIN TRB_LANCAMENTO LAN ON LAN.LAN_COD = BX.LAN_COD
	JOIN TRB_IPTU		IPT	ON IPT.TLA_COD = LAN.TLA_COD
	JOIN TRB_IMOVEL 	IMV ON IMV.IMV_COD = IPT.IMV_COD
GO

CREATE VIEW DBO.TRB_IPTU_TAXA_ARRECADACAO_VI AS
SELECT 
	TAXA.TLA_COD
	, TAXA.PLA_COD 
	, PLA_SIGLA
	, IIR_PARCELA
	, (CASE WHEN IIR_PARCELA = 0 THEN IIR_VALOR ELSE 0 END) AS IIR_VALOR_UNICA
	, (CASE WHEN IIR_PARCELA > 0 THEN IIR_VALOR ELSE 0 END) AS IIR_VALOR_DEMAIS
	, (CASE WHEN BXA_DATA_ARRECADACAO IS NOT NULL AND IIR_PARCELA = 0 THEN IIR_VALOR ELSE 0 END) AS IIR_VALOR_UNICA_PAGA
	, (CASE WHEN BXA_DATA_ARRECADACAO IS NOT NULL AND IIR_PARCELA > 0 THEN IIR_VALOR ELSE 0 END) AS IIR_VALOR_DEMAIS_PAGA
	FROM TRB_IPTU_TAXA 					TAXA 
	INNER JOIN GER_PLANO_DE_CONTAS_VI 	GPC1 ON GPC1.PLA_COD = TAXA.PLA_COD 
	INNER JOIN TRB_LANCAMENTO 			LANC ON LANC.TLA_COD = TAXA.TLA_COD AND LANC.LAN_PARCELA = IIR_PARCELA
	LEFT  JOIN TRB_BAIXA 				BXA  ON BXA.LAN_COD  = LANC.LAN_COD
GO